Skip Headers

Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Using SQL Datatypes in Application Development

This chapter discusses how to use SQL datatypes in database applications. Topics include the following:

- Representing Data with SQL Datatypes: Overview

- Representing Character Data

- Representing Numeric Data

- Representing Datetime Data

- Representing Specialized Data

- Representing Conditional Expressions as Data

- Identifying Rows by Address

- How Oracle Database Converts Datatypes

See Also: Oracle Database Application Developer's Guide - Object-Relational Features for information about more complex types, such as object types, varrays, and nested tables Oracle Database Application Developer's Guide - Large Objects for information about LOB datatypes Oracle Database PL/SQL User's Guide and Reference to learn about the PL/SQL datatypes. Many SQL datatypes are the same or similar in PL/SQL.

## Representing Data with SQL Datatypes: Overview

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle Database to treat values of one datatype differently from values of another datatype. For example, Oracle Database can add values of `NUMBER` datatype, but not values of `RAW` datatype.

Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. The datatypes supported by Oracle Database can be divided into the following categories:

- Oracle built-in datatypes, which include datatypes for characters, numbers, dates and times (known as datetime datatypes), raw data, large objects (LOBs), and row addresses (ROWIDs).ANSI datatypes and datatypes from the IBM products SQL/DS and DB2, which are usable in SQL statements that create tables and clustersUser-defined types, which use Oracle built-in datatypes and other user-defined datatypes as the building blocks of object types that model the structure and behavior of data in applicationsOracle-supplied types, which are SQL-based interfaces for defining new types

The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. You should not confuse Oracle Database built-in datatypes and user-defined types with external datatypes.

See Also: Oracle Database SQL Reference for complete reference information on the SQL datatypes Pro*COBOL Programmer's Guide and Pro*C/C++ Programmer's Guide for information on external datatypes, including how Oracle converts between them and built-in or user-defined types Oracle Database Concepts to learn about Oracle built-in datatypes

## Representing Character Data

This section contains the following topics:

- Representing Character Data: Overview

- Specifying Column Lengths as Bytes or Characters

- Choosing Between the CHAR and VARCHAR2 Datatypes

- Using Character Literals in SQL Statements

### Representing Character Data: Overview

You can use the following SQL datatypes to store alphanumeric data:

- `CHAR` and `NCHAR` datatypes store fixed-length character literals.

- `VARCHAR2` and `NVARCHAR2` datatypes store variable-length character literals.

- `NCHAR` and `NVARCHAR2` datatypes store Unicode character data only.

- `CLOB` and `NCLOB` datatypes store single-byte and multibyte character strings of up to (4 gigabytes - 1) * (the value obtained from `DBMS_LOB.GETCHUNKSIZE`).

- The `LONG` datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions. This datatype is provided only for backward compatibility with existing applications. In general, new applications should use `CLOB` and `NCLOB` datatypes to store large amounts of character data, and `BLOB` and `BFILE` to store large amounts of binary data.

See Also: Oracle Database Application Developer's Guide - Large Objects for information on `LOB` datatypes (including `CLOB` and `NCLOB` datatypes) and migration from `LONG` to `LOB` datatypes Oracle Database SQL Reference for restrictions on `LONG` datatypes

### Specifying Column Lengths as Bytes or Characters

You can specify the lengths of `CHAR` and `VARCHAR2` columns as either bytes or characters. The lengths of `NCHAR` and `NVARCHAR2` columns are always specified in characters, making them ideal for storing Unicode data, where a character might consist of multiple bytes.

Consider the following list of column length specifications:

- `id VARCHAR2(32 BYTE)`

  The `id` column contains only single-byte data, up to 32 bytes.

- `name VARCHAR2(32 CHAR)`

  The `name` column contains data in the database character set. If the database character set allows multibyte characters, then the 32 characters can be stored as more than 32 bytes.

- `biography NVARCHAR2(2000)`

  The `biography` column can represent 2000 characters in any Unicode-representable language. The encoding depends on the national character set, but the column can contain multibyte values even if the database character set is single-byte.

- `comment VARCHAR2(2000)`

  The representation of `comment` as 2000 bytes or characters depends on the initialization parameter `NLS_LENGTH_SEMANTICS`.

When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, then the number of bytes and the number of characters in a column is the same. If it is multibyte, however, then there generally is no such correspondence. A character might consist of one or more bytes, depending upon the specific multibyte encoding scheme and whether shift-in/shift-out control codes are present. To avoid overflowing buffers, specify data as `NCHAR` or `NVARCHAR2` if it might use a Unicode encoding that is different from the database character set.

See Also: Oracle Database Globalization Support Guide Oracle Database SQL Reference

### Choosing Between the CHAR and VARCHAR2 Datatypes

When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:

- Space usage

  To store data more efficiently, use the `VARCHAR2` datatype. The `CHAR` datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the `VARCHAR2` datatype does not add extra blanks.

- Comparison semantics

  Use the `CHAR` datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the `VARCHAR2` when trailing blanks are important in string comparisons.

- Future compatibility

  The `CHAR` and `VARCHAR2` datatypes are fully supported. At this time, the `VARCHAR` datatype automatically corresponds to the `VARCHAR2` datatype and is reserved for future use.

When an application interfaces with Oracle Database, there is a character set on the client and server side. Oracle Database uses the `NLS_LANGUAGE` parameter to automatically convert `CHAR`, `VARCHAR2`, and `LONG` data from the database character set to the character set defined for the user session, if these are different.

In the section "Datatype Comparison Rules," Oracle Database SQL Reference explains the comparison semantics that Oracle Database uses to compare character data. Because Oracle Database blank-pads values stored in `CHAR` columns but not in `VARCHAR2` columns, a value stored in a `VARCHAR2` column can take up less space than the same value in a `CHAR` column. For this reason, a full table scan on a large table containing `VARCHAR2` columns may read fewer data blocks than a full table scan on a table containing the same data stored in `CHAR` columns. If your application often performs full table scans on large tables containing character data, then you may be able to improve performance by storing data in `VARCHAR2` rather than in `CHAR` columns.

Performance is not the only factor to consider when deciding which datatype to use. Oracle Database uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle Database to ignore trailing blanks when comparing character values, then you must store these values in `CHAR` columns.

See Also: Oracle Database SQL Reference for more information on comparison semantics for these datatypes

### Using Character Literals in SQL Statements

Many SQL statements, functions, expressions, and conditions require you to specify character literal values. You can specify character literals with the following notations:

- Character literals with the `'``text``'` notation, as in the literals `'users01.dbf'` and `'Muthu''s computer'`.

- National character literals with the `N``'``text``'` or `n``'``text``'` notation, where `N` or `n` specifies the literal using the national character set. For example, `N'résumé'` is a National character literal.

  Oracle Database translates N-quoted text into the national character set by way of the database character set. If client-side characters do not have corresponding encoding in the database character set, then Oracle Database converts them into question marks. To avoid the potential loss of data during the text literal conversion, set the environment variable `$ORA_NCHAR_LITERAL_REPLACE` to `TRUE`. This setting transparently replaces the `N``'``text``'` internally and preserves the text literal for SQL processing.

The `UNISTR` function provides support for Unicode character literals by enabling you to specify the Unicode encoding value of characters in the string, as in `UNISTR('\1234')`. This technique is useful, for example, when inserting data into `NCHAR` columns. Because every character has a corresponding Unicode encoding, the client application can safely send character data to the server without data loss.

#### Quoting Character Literals

By default you must quote character literals in single-quotes, as in `'Hello'`. This technique can sometimes be inconvenient if the text itself contains single quotes. In such cases, you can also use the Q-quote mechanism, which enables you to specify `q` or `Q` followed by a single quote and then another character to be used as the quote delimiter. For example, the literal `q'#it's the "final" deadline#'` uses the pound sign (`#`) as a quote delimiter for the string `it's the "final" deadline`.

The Q-quote delimiter can be any single- or multibyte character except space, tab, and return. If the opening quote delimiter is a `[`, `{`, `<`, or `(` character, then the closing quote delimiter must be the corresponding `]`, `}`, `>`, or `)` character. In all other cases, the opening and closing delimiter must be the identical character.

The following character literals use the alternative quoting mechanism:

    q'(name LIKE '%DBMS_%%')'
    q'<'Data,' he said, 'Make it so.'>'
    q'"name like '['"'
    nq'ïŸ1234ï'

See Also: Oracle Database Globalization Support Guide to learn about national character sets Oracle Database SQL Reference to learn about character literals

## Representing Numeric Data

This section contains the following topics:

- What Are the Numeric Datatypes?

- Using Floating-Point Number Formats

- Using Comparison Operators for Native Floating-Point Datatypes

- Performing Arithmetic Operations with Native Floating-Point Datatypes

- Using Conversion Functions with Native Floating-Point Datatypes

- Client Interfaces for Native Floating-Point Datatypes

### What Are the Numeric Datatypes?

The following SQL datatypes store numeric data:

- `NUMBER`

- `BINARY_FLOAT`

- `BINARY_DOUBLE`

Use the `NUMBER` datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle Database platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, as well as zero, in a `NUMBER` column.

The `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively. Compared to the Oracle `NUMBER` datatype, arithmetic operations on floating-point data are usually faster for `BINARY_FLOAT` and `BINARY_DOUBLE`. Also, high-precision values require less space when stored as `BINARY_FLOAT` and `BINARY_DOUBLE`.

In client interfaces supported by Oracle Database, the native instruction set supplied by the hardware vendor performs arithmetic operations on `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes. The term native floating-point datatypes refers to datatypes including `BINARY_FLOAT` and `BINARY_DOUBLE` and to all implementations of these types in supported client interfaces.

The floating-point number system is a common way of representing and manipulating numeric values in computer systems. A floating-point number is characterized by the following components:

- Binary-valued sign

- Signed exponent

- Significand

- Base

A floating-point value is the signed product of its significand and the base raised to the power of its exponent, as shown in the formula in Example 3-1.

Example 3-1 Components of a Floating-Point Number

    (-1)sign . significand . base exponent

For example, the number `4.31` can be represented in the following expression:

    (-1)0 . 431 . 10 -2

The components of the preceding expression are as follows:

- `0` is the binary-valued sign

- `431` is the significant

- `10` is the base

- `-2` is the exponent

See Also: Oracle Database Concepts for information about the internal format for the `NUMBER` datatype Oracle Database SQL Reference for more information about the `BINARY_FLOAT` and `BINARY_DOUBLE` datatypes formats

### Using Floating-Point Number Formats

A floating-point number format specifies how components of a floating-point number are represented. The choice of representation determines the range and precision of the values the format can represent. By definition, the range is the interval bounded by the smallest and the largest values the format can represent and the precision is the number of digits in the significand.

Formats for floating-point values support neither infinite precision nor infinite range. There are a finite number of bits to represent a number and only a finite number of values that a format can represent. A floating-point number that uses more precision than available with a given format is rounded.

A floating-point number can be represented in a binary system (one that uses base 2), as in the IEEE 754 standard, or in a decimal system (one that uses base 10), such as Oracle `NUMBER`. The base affects many properties of the format, including how a numeric value is rounded.

For a decimal floating-point number format like Oracle `NUMBER`, rounding is done to the nearest decimal place (for example. 1000, 10, or 0.01). The IEEE 754 formats use a binary format for floating-point values and round numbers to the nearest binary place (for example: 1024, 512, or 1/64).

The native floating-point datatypes supported by the database round to the nearest binary place, so they are not satisfactory for applications that require decimal rounding. Use the Oracle `NUMBER` datatype for applications in which decimal rounding is required on floating-point data.

#### Using a Floating-Point Binary Format

The value of a floating-point number that uses a binary format is determined by the formula in Example 3-2.

Example 3-2 Components of a Floating-Point Number in Binary Format

    (-1)s 2E (b0 b1 b2 ... bp-1)

Table 3-1 describes the components of the formula.

Table 3-1 Components of the Binary Format for Floating-Point Numbers

Component | Specifies . . .
--- | ---
`s` | `0` or `1`
`E` | Any integer between `E`min and `E`max, inclusive (see Table 3-2)
`b`i | `0` or `1`, where the sequence of bits represents a number in base 2 (see Table 3-2)

The leading bit of the significand, b0, must be set (1), except for subnormal numbers (explained later). Consequently, the leading bit is not actually stored, so the formats provide N bits of precision although only N-1 bits are stored.

Note: The IEEE 754 specification also defines extended single-precision and extended double-precision formats, which are not supported by Oracle Database.

The parameters for these formats are described in Table 3-2.

Table 3-2 Summary of Binary Format Parameters

Parameter | Single-precision (32-bit) | Double-precision (64-bit)
--- | --- | ---
`p` | 24 | 53
`E`min | -126 | -1022
`E`max | +127 | +1023

The storage parameters for the formats are described in Table 3-3. The in-memory formats for single-precision and double-precision datatypes are specified by IEEE 754.

Table 3-3 Summary of Binary Format Storage Parameters

Datatype | Sign bits | Exponent bits | Significand bits | Total bits
--- | --- | --- | --- | ---
Single-precision | 1 | 8 | 24 (23 stored) | 32
Double-precision | 1 | 11 | 53 (52 stored) | 64

A significand is normalized when the leading bit of the significand is set. IEEE 754 defines denormal or subnormal values as numbers that are too small to be represented with an implied leading set bit in the significand. The number is too small because its exponent would be too large if its significand were normalized to have an implied leading bit set. IEEE 754 formats support subnormal values. Subnormal values preserve the following property:

if: x - y == 0.0 (using floating-point subtraction)

then: x == y

Table 3-4 shows the range and precision of the required formats in the IEEE 754 standard and those of Oracle `NUMBER`. Range limits are expressed here in terms of positive numbers; they also apply to the absolute value of a negative number. (The notation "number e exponent" used here stands for number multiplied by 10 raised to the exponent power: number . 10 exponent.)

Table 3-4 Range and Precision of IEEE 754 formats

Range and Precision | Single-precision 32-bitFoot 1 | Double-precision 64-bit1 | Oracle NUMBER Datatype
--- | --- | --- | ---
Max positive normal number | 3.40282347e+38 | 1.7976931348623157e+308 | < 1.0e126
Min positive normal number | 1.17549435e-38 | 2.2250738585072014e-308 | 1.0e-130
Max positive subnormal number | 1.17549421e-38 | 2.2250738585072009e-308 | not applicable
Min positive subnormal number | 1.40129846e-45 | 4.9406564584124654e-324 | not applicable
Precision (decimal digits) | 6 - 9 | 15 - 17 | 38 - 40

Footnote 1 These numbers are quoted from the IEEE Numerical Computation Guide.

See Also: Oracle Database SQL Reference, section "Numeric Literals", for information about literal representation of numeric values Oracle Database SQL Reference for more information about floating-point formats

#### Representing Special Values with Native Floating-Point Formats

IEEE 754 allows special values to be represented. These special values are as follows:

- Positive infinity (`+INF`)

- Negative infinity (`-INF`)

- Not-a-number (`NaN`)

- Positive zero (`+0`)

- Negative zero (`-0`)

`NaN` represent results of operations that are undefined. Many bit patterns in IEEE 754 represent `NaN`. Bit patterns can represent `NaN` with and without the sign bit set. IEEE 754 distinguishes between signalling `NaNs` and quiet `NaNs`.

IEEE 754 specifies behavior for when exceptions are enabled and disabled. Oracle Database does not allow exceptions to be enabled; the database behavior is that specified by IEEE 754 for when exceptions are disabled. In particular, Oracle Database makes no distinction between signalling and quiet `NaNs`. Programmers who use OCI can retrieve `NaN` values from Oracle Database; whether a retrieved `NaN` value is signalling or quiet depends on the client platform and beyond the control of Oracle Database.

IEEE 754 does not define the bit pattern for either type of `NaN`. Positive infinity, negative infinity, positive zero, and negative zero are each represented by a specific bit pattern.

Ignoring signs, there are the following classes of values, with each of the classes except for `NaN` greater than the one preceding it in the list:

- Zero

- Subnormal

- Normal

- Infinity

- `NaN`

In IEEE 754, `NaN` is unordered with other classes of special values and with itself.

##### Behavior of Special Values for Native Floating-Point Datatypes

When used with the database, special values of native floating-point datatypes behave as follows:

- All `NaNs` are quiet.

- IEEE 754 exceptions are not raised.

- `NaN` is ordered as follows:

  All non-`NaN` < `NaN`

  Any `NaN` == any other `NaN`

- -0 is converted to +0.

- All `NaNs` are converted to the same bit pattern.

  See Also: "Using Comparison Operators for Native Floating-Point Datatypes" for more information on `NaN` compared to other values

### Using Comparison Operators for Native Floating-Point Datatypes

Oracle Database defines the following comparison operators for operations involving floating-point datatypes:

- Equal to

- Not equal to

- Greater than

- Greater than or equal to

- Less than

- Less than or equal to

- Unordered

Note the following special cases:

- Comparisons ignore the sign of zero (`-0` is equal to, not less than, `+0`).

- In Oracle Database, `NaN` is equal to itself. `NaN` is greater than everything except itself. That is, `NaN == NaN` and `NaN >` x, unless `x` is `NaN`.

  See Also: "Behavior of Special Values for Native Floating-Point Datatypes" for more information on comparison results, ordering, and other behaviors of special values

### Performing Arithmetic Operations with Native Floating-Point Datatypes

Oracle Database defines operators for the following arithmetic operations:

- Multiplication

- Division

- Addition

- Subtraction

- Remainder

- Square root

You can define the mode used to round the result of the operation. Exceptions can be raised when operations are performed. Exceptions can also be disabled.

Formerly, Java required floating-point arithmetic to be exactly reproducible. IEEE 754 does not require such behavior. The standard allows for the result of operations, including arithmetic, to be delivered to a destination that uses a range greater than that used by the operands to the operation.

You can compute the result of a double-precision multiplication at an extended double-precision destination. When this is done, the result must be rounded as if the destination were single-precision or double-precision. The range of the result, that is, the number of bits used for the exponent, can use the range supported by the wider (extended double-precision) destination. This occurrence may result in a double-rounding error in which the least significant bit of the result is incorrect.

This state of affairs can only occur for double-precision multiplication and division on hardware that implements the IA-32 and IA-64 instruction set architecture. Thus, with the exception of this case, arithmetic for these datatypes is reproducible across platforms. When the result of a computation is `NaN`, all platforms produce a value for which `IS NAN` is true. However, all platforms do not have to use the same bit pattern.

### Using Conversion Functions with Native Floating-Point Datatypes

Oracle Database defines functions that convert between floating-point and other formats, including string formats that use decimal precision (precision may be lost during the conversion). For example, you can use the following functions:

- `TO_BINARY_DOUBLE`, which converts float to double, decimal (string) to double, and float or double to integer-valued double

- `TO_BINARY_FLOAT`, which converts double to float, decimal (string) to float, and float or double to integer-valued float

- `TO_CHAR`, which converts float or double to decimal (string)

- `TO_NUMBER`, which converts a float, double, or string to a number

Oracle Database can raise exceptions during conversion. The IEEE 754 specification defines the following exceptions:

- Invalid

- Inexact

- Divide by zero

- Underflow

- Overflow

Oracle Database does not raise these exceptions for native floating-point datatypes. Generally, situations that would raise an exception produce the values described in Table 3-5.

Table 3-5 Values Resulting from Exceptions

Exception | Value
--- | ---
Underflow | `0`
Overflow | `-INF`, `+INF`
Invalid Operation | `NaN`
Divide by Zero | `-INF`, `+INF`, `NaN`
Inexact | Any value – rounding was performed

### Client Interfaces for Native Floating-Point Datatypes

Oracle Database has implemented support for native floating-point datatypes in the following client interfaces:

- SQL

- PL/SQL

- OCI and OCCI

- Pro*C/C++

- JDBC

#### OCI Native Floating-Point Datatypes SQLT_BFLOAT and SQLT_BDOUBLE

The OCI API implements the IEEE 754 single precision and double precision native floating-point datatypes with the datatypes `SQLT_BFLOAT` and `SQLT_BDOUBLE` respectively. Conversions between these types and the SQL types `BINARY_FLOAT` and `BINARY_DOUBLE` are exact on platforms that implement the IEEE 754 standard for the C datatypes `FLOAT` and `DOUBLE`.

See Also: Oracle Call Interface Programmer's Guide

#### Native Floating-Point Datatypes Supported in Oracle OBJECT Types

Oracle Database supports the SQL datatypes `BINARY_FLOAT` and `BINARY_DOUBLE` as attributes of Oracle `OBJECT` types.

#### Pro*C/C++ Support for Native Floating-Point Datatypes

Pro*C/C++ supports the native `FLOAT` and `DOUBLE` datatypes using the column datatypes `BINARY_FLOAT` and `BINARY_DOUBLE`. You can use these datatypes in the same way that Oracle `NUMBER` datatype is used. You can bind the native C/C++ datatypes `FLOAT` and `DOUBLE` to `BINARY_FLOAT` and `BINARY_DOUBLE` types respectively by setting the Pro*C/C++ precompiler command line option `NATIVE_TYPES` to `Y` (yes) when you compile your application.

## Representing Datetime Data

This section contains the following topics:

- Representing Datetime Data: Overview

- Manipulating the Date Format

- Manipulating the Time Format

- Performing Date Arithmetic

- Converting Between Datetime Types

- Importing and Exporting Datetime Types

### Representing Datetime Data: Overview

Oracle Database supports the following datetime datatypes:

- `DATE`

- `TIMESTAMP`

- `TIMESTAMP WITH TIME ZONE`

- `TIMESTAMP WITH LOCAL TIME ZONE`

#### Using the DATE Datatype

Use the `DATE` datatype to store point-in-time values (dates and times) in a table. The `DATE` datatype stores the century, year, month, day, hours, minutes, and seconds.

#### Using the TIMESTAMP Datatype

Use the `TIMESTAMP` datatype to store values that are precise to fractional seconds. For example, an application that must decide which of two events occurred first might use `TIMESTAMP`. An application that specifies the time for a job might use `DATE`.

#### Using the TIMESTAMP WITH TIME ZONE Datatype

Because `TIMESTAMP WITH TIME ZONE` can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions.

#### Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype

Use `TIMESTAMP WITH LOCAL TIME ZONE` when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

The `TIMESTAMP WITH LOCAL TIME ZONE` type is appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system. It is generally inappropriate in three-tier applications because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. The Web server is the database client, so its local time is used.

#### Representing the Difference Between Datetime Values

Use the `INTERVAL DAY TO SECOND` datatype to represent the precise difference between two datetime values. For example, you might use this value to set a reminder for a time 36 hours in the future or to record the time between the start and end of a race. To represent long spans of time with high precision, you can use a large value for the days portion.

Use the `INTERVAL YEAR TO MONTH` datatype to represent the difference between two datetime values, where the only significant portions are the year and the month. For example, you might use this value to set a reminder for a date 18 months in the future, or check whether 6 months have elapsed since a particular date.

Oracle Database stores dates in its own internal format. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

See Also: Oracle Call Interface Programmer's Guide for a complete description of the Oracle Database internal date format

### Manipulating the Date Format

For input and output of dates, the standard Oracle Database default date format is `DD-MON-RR`. The `RR` datetime format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year.

As explained in Oracle Database SQL Reference, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. For example, the following format refers to the year 2004 in a query issued between 1950 and 2049, but to the year 2005 in a query issued between 2050 and 2099:

    '13-NOV-04'

#### Changing the Default Date Format

Use the following techniques to change the default date format:

- To change on an instance-wide basis, use the `NLS_DATE_FORMAT` parameter.

- To change during a session, use the `ALTER` `SESSION` statement.

To enter dates that are not in the current default date format, use the `TO_DATE` function with a format mask. For example:

    SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"FROM DUAL;

Be careful when using a date format such as `DD-MON-YY`. The `YY` indicates the year in the current century. For example, `31-DEC-92` is `December 31, 2092`, not `1992` as you might expect. If you want to indicate years in any century other than the current one, use a different format mask, such as the default `RR`.

See Also: Oracle Database Concepts for information about Julian dates. Oracle Database Julian dates might not be compatible with Julian dates generated by other date algorithms.

#### Displaying the Current Date and Time

Use the SQL function `SYSDATE` to return the system date and time. You can use the `FIXED_DATE` initialization parameter to set `SYSDATE` to a constant, which can be useful for testing.

By default, `SYSDATE` is printed without any BC or AD qualifier. You can add `BC` to the format string to print the date with `BC` or `AD` as appropriate:

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC')
    FROM DUAL;

    TO_CHAR(SYSDAT
    --------------
    24-JAN-2004 AD

### Manipulating the Time Format

Time is stored in the following 24-hour format:

    HH24:MI:SS

By default, the time in a `DATE` column is 12:00:00 A.M. (midnight) if no time portion is entered or if the `DATE` is truncated.

In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the `TO_DATE` function with a format mask indicating the time portion, as shown in Example 3-3.

Example 3-3 Indicating Time with the TO_DATE Function

    -- create test table
    CREATE TABLE birthdays
    ( Bname VARCHAR2(20),
      Bday  DATE
    );

    -- insert a row
    INSERT INTO birthdays (bname, bday)
    VALUES
    ( 'ANNIE',
      TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.')
    );

### Performing Date Arithmetic

Oracle Database provides a number of features to help with date arithmetic, so that you do not need to perform your own calculations on the number of seconds in a day, the number of days in each month, and so on. Some useful features include the following:

- `ADD_MONTHS` function, which returns the date plus the specified number of months.

- `SYSDATE` function, which returns the current date and time set for the operating system on which the database resides.

- `SYSTIMESTAMP` function, which returns the system date, including fractional seconds and time zone, of the system on which the database resides.

- `TRUNC` function, which when applied to a `DATE` value, trims off the time portion so that it represents the very beginning of the day (the stroke of midnight). By truncating two `DATE` values and comparing them, you can determine whether they refer to the same day. You can also use `TRUNC` along with a `GROUP BY` clause to produce daily totals.

- Arithmetic operators such as `+` and `-`. For example, `SYSDATE-7` refers to 7 days before the current system date.

- `INTERVAL` datatypes, which enable you to represent constants when performing date arithmetic rather than performing your own calculations. For example, you can add or subtract `INTERVAL` constants from `DATE` values or subtract two `DATE` values and compare the result to an `INTERVAL`.

- Comparison operators such as `>`, `<`, `=`, and `BETWEEN`.

### Converting Between Datetime Types

Oracle Database provides several useful functions that enable you to convert to a from datetime datatypes. Some useful functions include:

- `EXTRACT`, which extracts and returns the value of a specified datetime field from a datetime or interval value expression

- `NUMTODSINTERVAL`, which converts a `NUMBER` or expression that can be implicitly converted to a `NUMBER` value to an `INTERVAL DAY TO SECOND` literal

- `NUMTOYMINTERVAL`, which converts a `NUMBER` or expression that can be implicitly converted to a `NUMBER` value to an `INTERVAL YEAR TO MONTH` literal

- `TO_DATE`, which converts character data to a `DATE` datatype

- `TO_CHAR`, which converts `DATE` data to character data

- `TO_DSINTERVAL`, which converts a character string to an `INTERVAL DAY TO SECOND` value

- `TO_TIMESTAMP`, which converts character data to a value of `TIMESTAMP` datatype

- `TO_TIMESTAMP_TZ`, which converts character data to a value of `TIMESTAMP WITH TIME ZONE` datatype

- `TO_YMINTERVAL`, which converts a character string to an `INTERVAL YEAR TO MONTH` type

  See Also: Oracle Database SQL Reference for details about each function

### Importing and Exporting Datetime Types

`TIMESTAMP WITH TIME ZONE` and `TIMESTAMP WITH LOCAL TIME ZONE` values are always stored in normalized format, so that you can export, import, and compare them without worrying about time zone offsets. `DATE` and `TIMESTAMP` values do not store an associated time zone, and you must adjust them to account for any time zone differences between source and target databases.

## Representing Specialized Data

This section contains the following topics:

- Representing Geographic Data

- Representing Multimedia Data

- Representing Large Amounts of Data

- Representing Searchable Text

- Representing XML

- Representing Dynamically Typed Data

- Representing Data with ANSI/ISO, DB2, and SQL/DS Datatypes

### Representing Geographic Data

To represent Geographic Information System (GIS) or spatial data in the database, you can use Oracle Spatial features, including the type `MDSYS.SDO_GEOMETRY`. You can store the data in the database by using either an object-relational or a relational model. You can use a set of PL/SQL packages to query and manipulate the data.

See Also: Oracle Spatial User's Guide and Reference to learn how to use `MDSYS.SDO_GEOMETRY`

### Representing Multimedia Data

Oracle interMedia enables Oracle Database to store, manage, and retrieve images, audio, video, or other heterogeneous media data in an integrated fashion with other enterprise information. Oracle interMedia extends Oracle Database reliability, availability, and data management to multimedia content in traditional, Internet, electronic commerce, and media-rich applications.

Whether you store such multimedia data inside the database as `BLOB` or `BFILE` values, or store it externally on a Web server or other kind of server, you can use interMedia to access the data using either an object-relational or a relational model, and manipulate and query the data using a set of object types.

Oracle interMedia provides the `ORDAudio`, `ORDDoc`, `ORDImage`, `ORDImageSignature`, `ORDVideo`, and `SI_StillImage` object types and methods for the following purposes:

- Extracting metadata and attributes from multimedia dataRetrieving and managing multimedia data from Oracle interMedia, Web servers, file systems, and other serversPerforming manipulation operations on image data

See Also: Oracle interMedia Referenceto learn about the interMedia types

### Representing Large Amounts of Data

Oracle Database provides several datatypes for representing large amounts of data. These datatypes are grouped under the general category of Large Objects (LOBs). Table 3-6 describes the different LOBs.

Table 3-6 Large Object Datatypes

Datatype | Name | Description
--- | --- | ---
`BLOB` | Binary large object | Represents large amounts of binary data such as images, video, or other multimedia data.
`CLOB` | Character large object | Represents large amounts of character data. `CLOB` types are stored by using the database character set. Note that the database stores a `CLOB` up to 4,000 bytes inline as a `VARCHAR2`. If the `CLOB` exceeds this length, then the database moves the `CLOB` out of line.
`NCLOB` | National character set large objects | Represents large amounts of character data in National Character Set format.
`BFILE` | External large object | Stores objects in the operating system`'`s file system, outside of the database files or tablespace. Note that the `BFILE` type is read-only; other LOB types are read/write. `BFILE` objects are also sometimes referred to as external LOBs.

An instance of type `BLOB`, `CLOB`, or `NCLOB` can exist as either a persistent LOB instance or a temporary LOB instance. Persistent and temporary instances differ as follows:

- A temporary LOB instance is declared in the scope of your application.

- A persistent LOB instance is created and stored in the database.

With the exception of declaring, freeing, creating, and committing, operations on persistent and temporary LOB instances are performed the same way.

See Also: Oracle Database Application Developer's Guide - Large Objects for more details on using LOBs in applications

#### Using RAW and LONG RAW Datatypes

The `RAW` and `LONG` `RAW` datatypes store data that is not interpreted by Oracle Database, that is, it is not converted when moving data between different systems. These datatypes are intended for binary data and byte strings. For example, `LONG` `RAW` can store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.

Oracle Net and the Export and Import utilities do not perform character conversion when transmitting `RAW` or `LONG` `RAW` data. When Oracle Database automatically converts `RAW` or `LONG` `RAW` data to and from `CHAR` data, as is the case when entering `RAW` data as a literal in an `INSERT` statement, the database represents the data as one hexadecimal character representing the bit pattern for every four bits of `RAW` data. For example, one byte of `RAW` data with bits 11001011 is displayed and entered as `CB`.

You cannot index `LONG` `RAW` data, but you can index `RAW` data. In earlier releases, the `LONG` and `LONG RAW` datatypes were typically used to store large amounts of data. Use of these types is no longer recommended for new development. If your application still uses these types, migrate your application to use LOB types. Oracle recommends that you convert `LONG RAW` columns to binary LOB (`BLOB`) columns and convert `LONG` columns to character LOB (`CLOB` or `NCLOB`) columns. LOB columns are subject to far fewer restrictions than `LONG` and `LONG RAW` columns.

See Also: See Oracle Database Application Developer's Guide - Large Objects for information about the `BLOB` and `BFILE` datatypes See the Oracle Database SQL Reference for restrictions on `LONG` and `LONG RAW` datatypes

### Representing Searchable Text

Rather than writing low-level code to do full-text searches, you can use Oracle Text. It stores the search data in a special kind of index, and lets you query the data with operators and PL/SQL packages. This technology enables you to create your own search engine using data from tables, files, or URLs, and combine the search logic with relational queries. You can also search XML data this way with the XPath notation.

See Also: Oracle Text Application Developer's Guide for more information

### Representing XML

If you have information stored as files in XML format, or if you want to take an object type and store it as XML, then you can use the `XMLType` built-in type.

`XMLType` columns store their data as `CLOB`s. You can take an existing `CLOB`, `VARCHAR2`, or any object type, and call the `XMLType` constructor to turn it into an XML object.

When an XML object is inside the database, you can use queries to traverse it (using the XML XPath notation) and extract all or part of its data.

You can also produce XML output from existing relational data and split XML documents across relational tables and columns. You can use the following packages to transfer XML data into and out of relational tables:

- `DBMS_XMLQUERY`, which provides database-to-`XMLType` functionality

- `DBMS_XMLGEN`, which converts the results of a SQL query to a canonical XML format

- `DBMS_XMLSAVE`, which provides XML to database-type functionality

You can use the following SQL functions to process XML:

- `EXTRACT`, which applies a `VARCHAR2` XPath string and returns an `XMLType` instance containing an XML fragment

- `SYS_XMLAGG`, which aggregates all of the XML documents or fragments represented by an expression and produces a single XML document

- `SYS_XMLGEN`, which takes an expression that evaluates to a particular row and column of the database, and returns an instance of type `XMLType` containing an XML document

- `UPDATEXML`, which takes as arguments an `XMLType` instance and an XPath-value pair and returns an `XMLType` instance with the updated value

- `XMLAGG`, which takes a collection of XML fragments and returns an aggregated XML document

- `XMLCOLATTVAL`, which creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name

- `XMLCONCAT`, which takes as input a series of `XMLType` instances, concatenates the series of elements for each row, and returns the concatenated series

- `XMLELEMENT`, which takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the content of the element

- `XMLFOREST`, which converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments

- `XMLSEQUENCE`, which either takes as input an `XMLType` instance and returns a varray of the top-level nodes in the `XMLType`, or takes as input a `REFCURSOR` instance, with an optional instance of the `XMLFormat` object, and returns as an `XMLSequence` type an XML document for each row of the cursor

  `XMLTRANSFORM`, which takes as arguments an `XMLType` instance and an XSL style sheet, applies the style sheet to the instance, and returns an `XMLType`

See Also: Oracle XML DB Developer's Guide for details about the `XMLType` datatype Oracle XML Developer's Kit Programmer's Guide for information about client-side programming with XML Oracle Database SQL Reference for information about XML functions

### Representing Dynamically Typed Data

Some languages allow datatypes to change at runtime or let a program check the type of a variable. For example, C has the `union` keyword and the `void *` pointer, while Java has the `typeof` operator and wrapper types such as `Number`. Oracle Database includes features that enable you to create variables and columns that can hold data of any type and test such data values to determine their underlying representation. Using these features, a single table column can represent a numeric value in one row, a string value in another row, and an object in another row.

You can use the built-in type `SYS.ANYDATA` to represent values of any scalar or object type. This type is an object type with methods to bring in a scalar value of any type, and turn the value back into a scalar or object. In the same way, you can use the built-in type `SYS.ANYDATASET` to represent values of any collection type.

To manipulate and check type information, you can use `SYS.ANYTYPE` in combination with the `DBMS_TYPES` package. The program in Example 3-4 represents data of different underlying types in a table, then interprets the underlying type of each row and processes each value appropriately.

Example 3-4 Accessing Information in a SYS.ANYDATA Column

    -- This example defines and executes a PL/SQL procedure that
    -- uses methods built into SYS.ANYDATA to access information about
    -- data stored in a SYS.ANYDATA table column.
    DROP TYPE Employee_type FORCE;
    DROP TABLE mytab;
    CREATE OR REPLACE TYPE Employee_type AS OBJECT ( empno NUMBER,
      ename VARCHAR2(10) );
    /
    CREATE TABLE mytab ( id NUMBER, data SYS.ANYDATA );
    INSERT INTO mytab VALUES (1, SYS.ANYDATA.ConvertNumber(5));
    INSERT INTO mytab VALUES (2,
                              SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john')));
    COMMIT;
    CREATE OR REPLACE PROCEDURE p
    IS
      CURSOR cur IS SELECT id, data FROM mytab;
      v_id           mytab.id%TYPE;
      v_data         mytab.data%TYPE;
      v_type         SYS.ANYTYPE;
      v_typecode     PLS_INTEGER;
      v_typename     VARCHAR2(60);
      v_dummy        PLS_INTEGER;
      v_n            NUMBER;
      v_employee     Employee_type;
      non_null_anytype_for_NUMBER exception;
      unknown_typename            exception;
    BEGIN
      OPEN cur;
      LOOP
        FETCH cur INTO v_id, v_data;
        EXIT WHEN cur%NOTFOUND;

    /* The typecode is a number that signifies what type is represented by v_data.
       GetType also produces a value of type SYS.AnyType with methods you can call
       to find precision and scale of a number, length of a string, and so on. */

        v_typecode := v_data.GetType ( v_type /* OUT */ );

    /* Now we compare the typecode against constants from DBMS_TYPES to see what
       kind of data we have, and decide how to display it. */

        CASE v_typecode
          WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
            IF v_type IS NOT NULL
    -- This condition should never happen, but we check just in case.
              THEN RAISE non_null_anytype_for_NUMBER; END IF;
    -- For each type, there is a Get method.
            v_dummy := v_data.GetNUMBER ( v_n /* OUT */ );
            DBMS_OUTPUT.PUT_LINE (
              TO_CHAR(v_id) || ': NUMBER = ' || To_Char(v_n) );
          WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
            v_typename := v_data.GetTypeName();
    -- An object type's name is qualified with the schema name.
            IF v_typename NOT IN ( 'HR.EMPLOYEE_TYPE' )
    -- If we encounter any object type besides EMPLOYEE_TYPE, raise an exception.
              THEN RAISE unknown_typename; END IF;
            v_dummy := v_data.GetObject ( v_employee /* OUT */  );
            DBMS_OUTPUT.PUT_LINE (
              To_Char(v_id) || ': user-defined type = ' || v_typename ||
                ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
        END CASE;
      END LOOP;
      CLOSE cur;
    EXCEPTION
      WHEN non_null_anytype_for_NUMBER THEN
        RAISE_Application_Error ( -20000,
          'Paradox: the return AnyType instance FROM GetType ' ||
          'should be NULL for all but user-defined types' );
      WHEN unknown_typename THEN
        RAISE_Application_Error ( -20000, 'Unknown user-defined type ' ||
          v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE' );
    END;
    /

The query and procedure in Example 3-4 produce output like that shown in Example 3-5.

Example 3-5 Sample Output for Example 3-4

    SQL> SELECT t.data.gettypename() AS "Type Name" FROM mytab t;

    Type Name
    --------------------------------------------------------------------------------
    SYS.NUMBER
    HR.EMPLOYEE_TYPE

    SQL> EXEC p;
    1: NUMBER = 5
    2: user-defined type = HR.EMPLOYEE_TYPE ( 5555, john )

You can access the same features through the OCI interface by using the `OCIType`, `OCIAnyData`, and `OCIAnyDataSet` interfaces.

See Also: Oracle Database PL/SQL Packages and Types Reference for details about the `DBMS_TYPES` package Oracle Database Application Developer's Guide - Object-Relational Features for information and examples using the `ANYDATA`, `ANYDATASET`, and `ANYTYPE` types Oracle Call Interface Programmer's Guide for details about the OCI interfaces

### Representing Data with ANSI/ISO, DB2, and SQL/DS Datatypes

You can define columns of tables in Oracle Database by means of ANSI/ISO, DB2, and SQL/DS datatypes. Oracle Database internally converts such datatypes to Oracle datatypes.

The ANSI datatype conversions are shown in Table 3-7. The ANSI/ISO datatypes `NUMERIC`, `DECIMAL`, and `DEC` can specify only fixed-point numbers. For these datatypes, `s` defaults to `0`.

Table 3-7 ANSI Datatype Conversions to Oracle Datatypes

ANSI SQL Datatype | Oracle Datatype
--- | ---
`CHARACTER (``n``), CHAR (``n``)` | `CHAR (``n``)`
`NUMERIC (``p``,``s``), DECIMAL (``p``,``s``), DEC (``p``,``s``)` | `NUMBER (``p``,``s``)`
`INTEGER, INT, SMALLINT` | `NUMBER (38)`
`FLOAT (``p``)` | `FLOAT (``p``)`
`REAL` | `FLOAT (63)`
`DOUBLE PRECISION` | `FLOAT (126)`
`CHARACTER VARYING(``n``), CHAR VARYING(``n``)` | `VARCHAR2 (``n``)`
`TIMESTAMP` | `TIMESTAMP`
`TIMESTAMP WITH TIME ZONE` | `TIMESTAMP WITH TIME ZONE`

Table 3-8 shows the DB2 and SQL/DS conversions.

Table 3-8 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

DB2 or SQL/DS Datatype | Oracle Datatype
--- | ---
`CHARACTER (``n``)` | `CHAR (``n``)`
`VARCHAR (``n``)` | `VARCHAR2 (``n``)`
`LONG VARCHAR` | `LONG`
`DECIMAL (``p``,``s``)` | `NUMBER (``p``,``s``)`
`INTEGER, SMALLINT` | `NUMBER (38)`
`FLOAT (``p``)` | `FLOAT (``p``)`
`DATE` | `DATE`
`TIMESTAMP` | `TIMESTAMP`

The datatypes `TIME`, `GRAPHIC`, `VARGRAPHIC`, and `LONG` `VARGRAPHIC` of IBM products SQL/DS and DB2 have no corresponding Oracle datatype, and they cannot be used.

## Representing Conditional Expressions as Data

The Oracle Expression Filter feature enables you to store conditional expressions as data in the database. The Expression Filter provides a mechanism that you can use to place a constraint on a `VARCHAR2` column to ensure that the values stored are valid SQL `WHERE` clause expressions. This mechanism also identifies the set of attributes that are legal to reference in the conditional expressions.

For example, suppose you create a `traders` table in which row holds data for a stock trading account holder. You want to define a column that stores information about stocks each trader is interested in as a conditional expression. You follow these steps:

1. Create a table `traders` holds data for a stock trading account holder:

       CREATE TABLE traders
       ( name     VARCHAR2(50),
         email    VARCHAR2(50),
         interest VARCHAR2(50)
       );

2. Create the user-defined datatype `ticker` with attributes for the trading symbol, limit price, and amount of change in the stock price:

       CREATE OR REPLACE TYPE ticker
       AS OBJECT
       ( symbol VARCHAR2(20),
         price  NUMBER,
         change NUMBER
       );

3. Use the following PL/SQL block to create an attribute set `ticker` based on the `ticker` datatype:

       BEGIN
         DBMS_EXPFIL.CREATE_ATTRIBUTE_SET( attr_set  => 'ticker',
                                           from_type => 'YES' );
       END;

4. Associate the attribute set with the expression set stored in the database column `trader.interest` as follows:

       BEGIN
         DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (attr_set => 'ticker',
                                           expr_tab => 'traders',
                                           expr_col => 'interest');
       END;

   The preceding code places a constraint on the `interest` column that ensures the column stores valid conditional expressions.

5. Populate the table with trader names, email addresses and conditional expressions that represents a stock the trader is interested in at a particular price:

       INSERT INTO traders (name, email, interest)
         VALUES ('Vishu', 'vishu@abc.com', 'symbol = ''ABC'' AND price > 25');

6. Use the `EVALUATE` operator to identify the conditional expressions that evaluate to `TRUE` for a given data item. For example, the following query returns traders who are interested in a given stock quote `(symbol='ABC', price=31, change=5.2)`:

       SELECT Name, Email
       FROM Traders
       WHERE EVALUATE ( interest,
                        'symbol=>''ABC'',
                        price=>31,
                        change=>5.2'
                      ) = 1;

To speed up this type of query, you can optionally create an Oracle Expression Filter index on the `interest` column.

See Also: Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for details on Oracle Expression Filter

## Identifying Rows by Address

Each row in a database table has an address called a rowid. You can examine a row address by querying the pseudocolumn `ROWID`, whose values are strings representing the address of each row. These strings have the datatype `ROWID` or `UROWID`. You can also create tables and clusters that contain actual columns having the `ROWID` datatype. Oracle Database does not guarantee that the values of such columns are valid rowids.

Rowid values are important for application development for the following reasons:

- They are the fastest way to access a single row.

- They can show you how the rows in a table are stored.

- They are unique identifiers for rows in a table.

See Also: Oracle Database Concepts for general information about the `ROWID` pseudocolumn and the `ROWID` datatype Oracle Database SQL Reference to learn about the `ROWID` pseudocolumn

### Querying the ROWID Pseudocolumn

Each table in Oracle Database has a pseudocolumn named `ROWID`. If the row is too large to fit within a single data block, then `ROWID` identifies the initial row piece. Although rowids are usually unique, different rows can have the same rowid if they are in the same data block but in different clustered tables.

The following SQL statements return the `ROWID` pseudocolumn of the row of the `hr.employees` table that satisfies the query, and inserts it into the `t_tab` table:

    CREATE TABLE t_tab (col1 ROWID);
    INSERT INTO t_tab
      SELECT ROWID
      FROM hr.employees
      WHERE employee_id = 7499;

Note: Although you can use the `ROWID` pseudocolumn in the `SELECT` and `WHERE` clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the `ROWID` pseudocolumn.

### Accessing the ROWID Datatype

In tables that are not index-organized and foreign tables, the values of the `ROWID` pseudocolumn have the datatype `ROWID`. The format of this datatype is either extended or restricted.

#### Restricted ROWID

Internally, the `ROWID` is a structure that holds information that the database server needs to access a row. The restricted internal `ROWID` is 6 bytes on most platforms. Each restricted rowid includes the following data:

- Datafile identifier

- Block identifier

- Row identifier

The restricted `ROWID` pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the `ROWID`.

#### Extended ROWID

The extended `ROWID` datatype includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal `ROWID` is 10 bytes on most platforms.

Data in an extended `ROWID` pseudocolumn is returned to the client application in the form of an 18-character string (for example, `"AAAA8mAALAAAAQkAAA"`), which represents a base 64 encoding of the components of the extended `ROWID` in a four-piece format, `OOOOOOFFFBBBBBBRRR`. Extended rowids are not available directly. You can use a supplied package, `DBMS_ROWID`, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

See Also: Oracle Database PL/SQL Packages and Types Reference for information about the `DBMS_ROWID` package

#### External Binary ROWID

Some client applications use a binary form of the `ROWID`. For example, OCI and some precompiler applications can map the `ROWID` datatype to a 3GL structure on bind or define calls. The size of the binary `ROWID` is the same for extended and restricted `ROWID`s. The information for the extended `ROWID` is included in an unused field of the restricted `ROWID` structure.

The format of the extended binary `ROWID`, expressed as a C struct, is as follows:

    struct riddef {
        ub4    ridobjnum; /* data obj#--this field is
                             unused in restricted ROWIDs */
        ub2    ridfilenum;
        ub1    filler;
        ub4    ridblocknum;
        ub2    ridslotnum;
    }

### Accessing the UROWID Datatype

The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Oracle provides these tables with logical row identifiers, called logical rowids. Rowids of foreign tables, such as DB2 tables accessed through a gateway, are not standard Oracle Database rowids. Oracle provides foreign tables with identifiers called foreign rowids.Oracle Database uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Both types of urowid are stored in the `ROWID` pseudocolumn, as are the physical rowids of heap-organized tables.Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The `ROWID` pseudocolumn of an index-organized table has a datatype of `UROWID`. You can access this pseudocolumn as you would the `ROWID` pseudocolumn of a heap-organized table (that is, using a `SELECT` `... ROWID` statement). If you want to store the rowids of an index-organized table, then you can define a column of type `UROWID` for the table and retrieve the value of the `ROWID` pseudocolumn into that column.

## How Oracle Database Converts Datatypes

In some cases, Oracle Database allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle Database can use various SQL functions to automatically convert data to the expected datatype.

See Also: Oracle Database SQL Reference for details about datatype conversion

### Datatype Conversion During Assignments

The datatype conversion for an assignment succeeds if Oracle Database can convert the datatype of the value used in the assignment to that of the assignment target.

For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:

    CREATE PACKAGE Test_Pack AS var1 CHAR(5); END;
    CREATE TABLE Table1_tab (col1 NUMBER);

- `variable := expression`

  The datatype of `expression` must be either the same as, or convertible to, the datatype of `variable`. For example, Oracle Database automatically converts the data provided in the following assignment within the body of a stored procedure:

      VAR1 := 0;

- `INSERT` `INTO Table1_tab VALUES` `(expression1`, `expression2`, ...`)`

  The datatypes of `expression1`, `expression2`, and so on, must be either the same as, or convertible to, the datatypes of the corresponding columns in `Table1_tab`. For example, Oracle Database automatically converts the data provided in the following `INSERT` statement for `Table1_tab`:

      INSERT INTO Table1_tab VALUES ('19');

- `UPDATE` `Table1_tab` `SET` `column` = `expression`

  The datatype of `expression` must be either the same as, or convertible to, the datatype of `column`. For example, Oracle Database automatically converts the data provided in the following `UPDATE` statement issued against `Table1_tab`:

      UPDATE Table1_tab SET col1 = '30';

- `SELECT` `column` `INTO` `variable` `FROM` `Table1_tab`

  The datatype of `column` must be either the same as, or convertible to, the datatype of `variable`. For example, Oracle Database automatically converts data selected from the table before assigning it to the variable in the following statement:

      SELECT Col1 INTO Var1 FROM Table1_tab WHERE Col1 = 30;

### Datatype Conversion During Expression Evaluation

For expression evaluation, Oracle Database can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to `NUMBER`, and operands to string functions are converted to `VARCHAR2`.

Oracle Database can automatically convert the following:

- `VARCHAR2` or `CHAR` to `NUMBER`

- `VARCHAR2` or `CHAR` to `DATE`

Character to `NUMBER` conversions succeed only if the character string represents a valid number. Character to `DATE` conversions succeed only if the character string satisfies the session default format, which is specified by the initialization parameter `NLS_DATE_FORMAT`.

Some common types of expressions follow:

- Simple expressions, such as:

      commission + '500'

- Boolean expressions, such as:

      bonus > salary / '10'

- Function and procedure calls, such as:

      MOD (counter, '2')

- `WHERE` clause conditions, such as:

      WHERE hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')

- `WHERE` clause conditions, such as:

      WHERE rowid = 'AAAAaoAATAAAADAAA'

In general, Oracle Database uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.

In assignments of the form:

    variable := expression

Oracle Database first evaluates expression using the conversion rules for expressions; expression can be as simple or complex as desired. If it succeeds, then the evaluation of expression results in a single value and datatype. Then, Oracle Database tries to assign this value to the target variable using the conversion rules for assignments.